﻿CREATE PROCEDURE [dbo].[spAlarm_Alerts]
(
	@startDate					date	= NULL,
	@endDate					date	= NULL,
	@numberMaxAlarm				int		= 20
)
AS

--- Ensure that there is a value for both start and end date
IF @startDate IS NULL AND @endDate IS NULL
BEGIN
	SET	@endDate = DATEADD(dd, 1, (CAST(GETDATE() as date)))
	SET @startDate = DATEADD(month, DATEDIFF(month, 0, @endDate), 0)
END
ELSE IF @startDate IS NULL
	SET @startDate = DATEADD(month, DATEDIFF(month, 0, @endDate), 0)
ELSE IF @endDate IS NULL
	SET	@endDate = DATEADD(dd, 1, @startDate)
ELSE IF @endDate IS NOT NULL
	SET	@endDate = DATEADD(dd, 1, @endDate) --- adding a day so that the requested date is part of the results


SELECT		ssd.[ssd_type_ro] [Environment],
			app.[app_name] [Application],
			UPPER(ssd.[ssd_sqlserver_name_ro]) [SQL Server Name],
			CAST(alt.[alt_log_date] as date) [alt_log_date],
			[alt_name] [Alert],
			[alt_severity] [Severity],
			[alt_message_id] [Message ID],
			SUM([alt_occurrence_count]) [Count]
FROM		[dbo].[tblWH_Alerts] alt
JOIN		[dbo].[tblMSX_server_discovery] ssd ON alt.[alt_ssd_id] = ssd.[ssd_id]
JOIN		[dbo].[tblMSX_application] app ON ssd.[ssd_app_id] = app.[app_id]
WHERE		[alt_log_date] >= @startDate
			AND [alt_log_date] < @endDate
GROUP BY	ssd.[ssd_type_ro],
			app.[app_name],
			UPPER(ssd.[ssd_sqlserver_name_ro]),
			CAST(alt.[alt_log_date] as date),
			[alt_name],
			[alt_severity],
			[alt_message_id]
HAVING		SUM([alt_occurrence_count]) >= @numberMaxAlarm